USE [DYBBERPDB]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_OrderReport]'))
DROP VIEW [dbo].[BView_OrderReport]
GO
CREATE VIEW [dbo].[BView_OrderReport]
AS
SELECT tb_ErpOrder.ID,dbo.tb_ErpOrder.Ord_DividedShop, dbo.tb_ErpOrder.Ord_Number, tb_ErpOrderDigital.Ordv_ViceNumber, 
dbo.tb_ErpOrder.Ord_Class, Ord_Type, Ord_OrderClass,Ordv_DigitalNumber,
CASE Ord_SinceOrderNumber WHEN '' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS ������, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS ��������, 

Cus_Name AS �ͻ�����,
Cus_Name_py AS �ͻ�ƴ��,
Cus_Sex_cs AS �ͻ��Ա�,
Cus_Telephone AS �ͻ��绰,
[Age_String] AS ����,
[Age_Day]  AS ��,
[Age_Year] AS ��,

dbo.tb_ErpOrder.Ord_PhotographyCategory AS ��ϵ���, 
dbo.tb_ErpOrder.Ord_CustomerSource AS �ͻ���Դ, 
dbo.tb_ErpOrder.Ord_SeriesName AS ��ϵ����, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS ��ϵ�۸�, 
(select stuff((select ',' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber = Ord_Number for xml path('')),1,1,'')) as �ӵ���,
dbo.fn_ChineseToSpell((select stuff((select ','+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber = Ord_Number for xml path('')),1,1,''))) as �ӵ���ƴ��,
(case when Ord_Type = '1' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '' end) as ��������,
(select stuff((select ',' + dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_Photographer!= '' group by Ordpg_Photographer for xml path('')),1,1,'')) as ��Ӱʦ,
(select stuff((select ',' + dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_MakeupArtist!= '' group by Ordpg_MakeupArtist for xml path('')),1,1,'')) as ��ױʦ,
(select top 1 dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordpg_PhotographyTime)) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyTime is not null order by Ordpg_PhotographyTime DESC) AS ����ʱ��, 
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = '2') > 0 then '������' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = '1') > 0 then 'OK' else 'δ��' end end) AS ����״̬,
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital.Ordv_EarlyRepairName) AS ����ʦ, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_EarlyRepairTime)) AS ����ʱ��, 
dbo.fn_CheckOrderEarlyRepairStatus(tb_ErpOrderDigital.Ordv_EarlyRepairStatus) AS ����״̬, 
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital.Ordv_FilmSelectionName) AS ѡƬʦ, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ordv_FilmSelectionTime)) AS ѡƬʱ��, 
dbo.fn_CheckOrderFilmSelectionStatus(tb_ErpOrderDigital.Ordv_FilmSelectionStatus) AS ѡƬ״̬, 
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital.Ordv_DesignerName) AS ���ʦ, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderDigital.Ordv_DesignerTime)) AS ���ʱ��, 
dbo.fn_CheckOrderDesignerStatus(tb_ErpOrderDigital.Ordv_DesignerStatus) AS ���״̬, 
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital.Ordv_RefinementName) AS ����ʦ, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderDigital.Ordv_RefinementTime)) AS ����ʱ��, 
dbo.fn_CheckOrderRefinementStatus(tb_ErpOrderDigital.Ordv_RefinementStatus) AS ����״̬, 
dbo.fn_CheckUserIDGetUserName(tb_ErpOrderDigital.Ordv_LookDesignName) AS �����ʦ, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderDigital.Ordv_LookDesignTime)) AS �����ʱ��, 
dbo.fn_CheckOrderLookDesignStatus(tb_ErpOrderDigital.Ordv_LookDesignStatus) AS �����״̬, 
tb_ErpOrderDigital.Ordv_LookDesignClaim AS �����Ҫ��, 
[dbo].[fun_GetPickupStatusStatus]([dbo].[Vw_OrderProductPickupView].[OPlist_PickupStatus],[dbo].[Vw_OrderProductPickupView].productCount) AS ȡ��״̬,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime([dbo].[Vw_OrderProductPickupView].OPlist_PickupTime)) AS ȡ������, 
dbo.tb_ErpOrder.Ord_Remark AS ��ע, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrder.Ord_CreateName) AS ¼��Ա, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Ord_CreateDateTime)) AS �µ�����,
Ord_CreateDateTime AS �µ����ڲ�ѯ,
(SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital WHERE (Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM 
dbo.tb_ErpOrder LEFT JOIN dbo.tb_ErpOrderDigital  ON dbo.tb_ErpOrder.Ord_Number = tb_ErpOrderDigital.Ordv_Number
left join [dbo].[Vw_OrderProductPickupView] on dbo.tb_ErpOrderDigital.Ordv_ViceNumber=[dbo].[Vw_OrderProductPickupView].OPlist_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
where CONVERT(int,Ord_Type) < 3 
GO